const sqlite3 = require('sqlite3').verbose();
const path = require('path');

// 创建数据库连接
const dbPath = path.join(__dirname, '../../resources/db/db.database');
const db = new sqlite3.Database(dbPath);

// 初始化数据库
db.serialize(() => {
  // 创建用户表
  db.run(`CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE NOT NULL,
    password TEXT NOT NULL,
    role TEXT NOT NULL DEFAULT 'user', -- 'admin' 或 'user'
    phone TEXT NULL, -- 添加手机号字段
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )`);

  // 创建用户课程进度表
  db.run(`CREATE TABLE IF NOT EXISTS user_course_progress (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    is_completed BOOLEAN DEFAULT 0,
    completion_percentage REAL DEFAULT 0.0,
    started_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    completed_at DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users (id),
    UNIQUE (user_id, course_id)
  )`);

  // 创建用户课时进度表
  db.run(`CREATE TABLE IF NOT EXISTS user_lesson_progress (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    lesson_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    is_completed BOOLEAN DEFAULT 0,
    completion_percentage REAL DEFAULT 0.0,
    time_spent INTEGER DEFAULT 0, -- 学习时长（秒）
    started_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    completed_at DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users (id)
    UNIQUE (user_id, lesson_id, course_id)
  )`);

  // 创建用户句子学习记录表
  db.run(`CREATE TABLE IF NOT EXISTS user_sentence_progress (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    sentence_id INTEGER NOT NULL,
    lesson_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    is_mastered BOOLEAN DEFAULT 0, -- 是否掌握
    times_practiced INTEGER DEFAULT 0, -- 练习次数
    last_practiced_at DATETIME NULL, -- 最后练习时间
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users (id),
    UNIQUE (user_id, sentence_id,lesson_id, course_id)
  )`);

  // 创建用户词汇学习记录表
  db.run(`CREATE TABLE IF NOT EXISTS user_vocabulary (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    vocabulary TEXT NOT NULL,
    is_learned BOOLEAN DEFAULT 0, -- 是否学会
    is_favorite BOOLEAN DEFAULT 0, -- 是否收藏
    times_encountered INTEGER DEFAULT 1, -- 遇到次数
    times_practiced INTEGER DEFAULT 0, -- 练习次数
    last_encountered_at DATETIME DEFAULT CURRENT_TIMESTAMP, -- 最后遇到时间
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users (id),
    UNIQUE (user_id, vocabulary)
  )`);
  // 创建匿名用户表
  db.run(`CREATE TABLE IF NOT EXISTS anonymous_users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    fingerprint TEXT UNIQUE NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
  );`);
  // 创建匿名用户访问记录表
  db.run(`CREATE TABLE IF NOT EXISTS visits (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    anonymous_user_id INTEGER,
    page_url TEXT NOT NULL,
    visit_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (anonymous_user_id) REFERENCES anonymous_users (id)
  );`);

  // 创建管理员用户（如果不存在）
  const stmt = db.prepare(`INSERT OR IGNORE INTO users (username, password, role) VALUES (?, ?, ?)`);
  stmt.run('admin', 'admin123', 'admin');
  stmt.finalize();
});

module.exports = db;